/**
 * 
 */
package javacommon.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;



/**
 * @author Administrator
 *
 */
public class ExcelUtils {
	
	private static HSSFWorkbook workbook;   
    private  static Sheet sheet;   
    //private ArrayList dataList;   
    //private String[] titles;   
    private  static int rowIndex = 0;
    
    
    public static List parseExcel(File file)
	{
		List list = new ArrayList();
		try
		{
		InputStream is = new FileInputStream(file);
		workbook = new HSSFWorkbook(is);
		sheet = workbook.getSheetAt(0);
		
		//列数是固定的
		int columns = 14;
		int row = sheet.getPhysicalNumberOfRows();
		
		//Customers vo = null;
		
		//读取Excel中的数据,组装成list
		for(int i =1; i<row;i++)
		{
			Map map  = new HashMap();
			/*for(int j=0;j<columns;j++)
			{
				map.put("", sheet.getRow(i).getCell(j).getStringCellValue());
			}*/
			
			map.put("name", sheet.getRow(i).getCell(0).getStringCellValue());
			map.put("address", sheet.getRow(i).getCell(1).getStringCellValue());
			map.put("count", sheet.getRow(i).getCell(2).getStringCellValue());
			map.put("city", sheet.getRow(i).getCell(3).getStringCellValue());
			map.put("desc", sheet.getRow(i).getCell(4).getStringCellValue());
			
			list.add(map);
			
		}
		}catch(Exception e)
		{
			
		}		
		return list;
	}
    
    private static  void createHead(int columnCount)
    {
    	rowIndex = 0;   
        
        
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,  columnCount));   
        Row headRow0 = sheet.createRow(rowIndex++);   
        headRow0.setHeightInPoints((short)90);
        createHeadCell(null,headRow0.createCell((short) 0), "Quotation");
        

        
        InputStream is;
        int pictureIdx=0 ;
		try {
			is = new FileInputStream("D:/logo.gif");
		
        byte[] bytes = IOUtils.toByteArray(is);
        pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        is.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
        CreationHelper helper = workbook.getCreationHelper();

        // Create the drawing patriarch.  This is the top level container for all shapes. 
        Drawing drawing = sheet.createDrawingPatriarch();
        //add a picture shape
        ClientAnchor anchor = helper.createClientAnchor();
        
        //HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short)0,0,(short)10,10);; 
        //set top-left corner of the picture,
        //subsequent call of Picture#resize() will operate relative to it
        anchor.setCol1(columnCount);
        anchor.setRow1(0);
        //anchor.setCol2(columnCount);
        //anchor.setRow2(0);
        
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        pict.resize();

    }
    
    private static  void createTitle(String[] titles)
    {
    	int i=0;   
        Row row = sheet.createRow(rowIndex++);   
        //row.
        //填充标题   
        for (String  s:titles){   
            Cell cell = row.createCell(i);   
            cell.setCellValue(s);  
            CellStyle cs = workbook.createCellStyle();
            cs.setWrapText(true);
            cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            cs.setAlignment(CellStyle.ALIGN_CENTER);
            cs.setFont(createTitleFonts());
            cell.setCellStyle(cs);
            i++;   
        }
    }
    
    private  static void createFoot()
    {
    	
    }
    
    private  static void createBody(List dataList)
    {
    	for (Iterator iterator = dataList.iterator(); iterator.hasNext();) 
    	{
    		Row row = sheet.createRow(rowIndex++);
    		//row.
			Object[] object = (Object[]) iterator.next();
			for (int i = 0; i < object.length; i++) {
				if(object[i] instanceof Number)
				{
					row.createCell(i).setCellValue(object[i]+"");
				}
				else if(object[i] instanceof Integer)
				{
					row.createCell(i).setCellValue(Integer.parseInt(object[i]+""));
				}
				else if(object[i] instanceof Date)
				{
					
				}
				else
				{
					row.createCell(i).setCellValue(object[i]+""); 
				}
			}
			
		}
    }
    
    private  static void createCell(CellStyle cellStyle, Cell cell, Object  value)   
    {     
    	cellStyle = workbook.createCellStyle();
    	cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    	//cellStyle.set
    	//cellStyle.setFont(createFonts());
        cell.setCellValue(value+"");  
        cell.setCellStyle(cellStyle);

    } 
    
    private  static void createHeadCell(CellStyle cellStyle, Cell cell, Object  value)   
    {     
    	cellStyle = workbook.createCellStyle();
    	cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    	cellStyle.setFont(createFonts());
        cell.setCellValue(value+"");  
        cell.setCellStyle(cellStyle);

    }
    
    public static Font createFonts(){   
        //创建Font对象   
        Font font = workbook.createFont();   
        //设置字体   
        font.setFontName("Verdana");   
        //着色   
        font.setColor(HSSFColor.BLACK.index);   
        //斜体   
        //font.setItalic(true);   
        //字体大小   
        font.setFontHeightInPoints((short)22);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        return font;   
    }
    public static Font createTitleFonts()
    {
    	Font font = workbook.createFont();   
        //设置字体   
        font.setFontName("Verdana");   
        //着色   
        font.setColor(HSSFColor.BLUE.index);   
        //斜体   
        //font.setItalic(true);   
        //字体大小   
        font.setFontHeightInPoints((short)8);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        return font;
    }
    
    public static Workbook createExcel(String sheetName,String[] titles,List dataList,int columnCount)
    {
    	workbook = new HSSFWorkbook();  
    	sheet = workbook.createSheet(sheetName);
    	
    	//createHead(columnCount);
    	createTitle(titles);
    	createBody(dataList);
    	
    	for (int i = 0; i < titles.length; i++) {
    		sheet.autoSizeColumn((short)i);
		}
    	return workbook;
    }
    
    public static void writeToResponse(String sheetName,String[] titles,List dataList,int columnCount,HttpServletResponse response,String fileName)
    {
    	createExcel(sheetName,titles,dataList,columnCount);
    	try {
    		fileName = URLEncoder.encode(fileName, "UTF-8");   
    		response.reset();   
    	    response.setContentType("application/download");   
    	    response.setHeader("Content-Disposition", "attachment; filename=" + fileName); 
			workbook.write(response.getOutputStream());
			response.getOutputStream().flush();
			response.getOutputStream().close();
			
		} catch (IOException e) {
			e.printStackTrace();
		}   
    }

		    /**  
	     * @param args  
	     */  
	    public static void main(String[] args) throws Exception {
	    	
	    	File file = new File("d:\\ping-pong.xls"); 
	    	
	    	List<Map> list = ExcelUtils.parseExcel(file);
	    	ConnectionManager cm=new ConnectionManager();
			Connection conn=cm.dbConnect();
	    	for(Map map: list){
	    		//cm.doInsert(conn, map,5);
	    	}
	  	  
	    }
}
